1. Field of the Invention
The present invention relates to the processing and optimization of database queries for increased execution performance. More particularly, the present invention relates to query optimization in a heterogeneous database environment where a local database system appears and responds to clients as if having at least some tables locally that are actually on remote database systems.
2. Present State of the Art
An important component of the query planning and optimization process in a heterogeneous database system is to determine the portions of the heterogeneous query that can be executed at the remote database system, i.e., pushdown analysis. Accurate pushdown analysis is important for heterogeneous query optimization because of the need to clearly identify the portions of the query that can be executed at the remote database systems in order to generate a complete query execution plan.
An optimizer will consider many alternatives as it generates query plans. Generally speaking, the more alternatives for an optimizer to explore, the better chances of arriving at the most optimal final plan. In the heterogeneous database environment, remotely executeable or xe2x80x9cpushdownablexe2x80x9d queries or portions of queries are in many instances very efficient and hence make good alternatives for an optimizer to consider.
Conservative pushdown analysis could result in query plans that are not optimal since potential alternatives are never considered by a query optimizer. When there is incorrect pushdown analysis, an optimizer could generate query plans that are infeasible due to the capability limitations of the remote database system.
In some heterogeneous database systems, the portions of the heterogeneous query that can be executed at the remote database system is determined by a pushdown analysis that is typically effective only for plan independent features of the query. In other words, only those features of the query that would always be pushdownable regardless of how the optimizer generated the query plan would in fact be considered pushed down.
This type pushdown analysis is performed before query optimization, and is not associated with the plan enumeration and evaluation. This approach of checking and indicating various portions of the query that can be executed at the remote database system cannot always accurately capture the portions of the query that are really pushdownable because some of the pushdownabilities are dependent upon how the query is planned for execution. What is needed are better ways to capture pushdownable portions of queries in a heterogeneous database environment.
One aspect of the present invention provides a way of increasing the amount of pushdownability by doing pushdown analysis during query plan generation that takes into account the actual query plan in determining what portions of a query may or may not be pushdownable. This in turn may lead to more optimal query execution plans since the optimizer may consider more alternatives.
Additional aspects and benefits of the invention will be set forth in the description that follows, and in part will be obvious from the description, or may be learned by the practice of the invention. The benefits of the invention may be realized and obtained by the combinations particularly pointed out in the appended claims.
In accordance with the invention as embodied and broadly described herein, a method, computer product, and system for pushdown analysis during query plan generation is provided.
Essentially, for each portion of the query plan that is generated by an optimizer, a test is made to see whether that portion of the query plan is pushdownable or not. If so, that portion of the query plan is xe2x80x9cduplicated.xe2x80x9d The original portion is processed and cost parameters are generated from the perspective of it being executed locally.
The duplicated portion is also processed but the cost parameters are generated from the perspective of it being pushed down and executed at the remote database system. The optimizer now has an extra alternative to evaluate as it continues plan generation and enumeration.
The optimizer considers the various permutations of the tables and subquery results to be combined along with the predicates that can be applied on the result. This allows for those pushdownabilities that are plan dependent to be evaluated under the specific context being planned.